--权限表
CREATE TABLE rk_privilege (
	privilege_id	int identity not null,   	--权限id 
	privilege_name varchar(100),				--显示名称
	privilege_key  varchar(200),				--操作权限
	remark		varchar(200),					--备注 
	status int,	   								--是否有效 
	constraint "PK_rk_privilege_id" primary key ("privilege_id")
)charset=gbk

go
--	角色和菜单关联表
CREATE TABLE rk_role_menu (
	id int identity not null,
	role_id int not null,
	menu_id int not null,
	constraint "PK_rk_role_menu_id" primary key ("id")
)
go
--用户和角色关联表
 CREATE TABLE rk_user_role (
 	 id  int identity not null,
 	 user_id int not null,
 	 role_id int,
 	 constraint "PK_rk_user_role_id" primary key ("id")
)
 go
 --角色和权限关联表
 CREATE TABLE rk_role_privilege (
 	id int identity not null,
 	role_id int,
 	privilege_id int,
 	constraint "PK_rk_role_privilege" primary key ("id")
)
  go
  
 --创建角色表
CREATE TABLE rk_role (
	role_id int identity not null,
	role_name varchar(100) not null,
	create_user varchar(50),
	create_time varchar(50),
	remark varchar(200),
	status int  
	constraint "PK_rk_role_id" primary key ("role_id")
)
go 
create table rk_app_user
(
   "id" int identity not null,
   "user_name" nvarchar(50) not null,
   "real_name" nvarchar(50),
   "user_sex" int null,
   "user_id_no" varchar(30) null,
   "user_phone" varchar(30) null,
   "user_zip_code" varchar(10) null,
   "user_email" varchar(100) null,
   "user_pwd" varchar(50) not null,
   "user_address" varchar(200) not null,
   "qq" varchar(30) null,
   "credit" int,
   "rank" int null,
   "parent_id" int,
   "status" int not null,
   "login_time" varchar(50),
   "login_status" int,
   "create_time" varchar(50) null,
   "login_version" varchar(50) null
)

create table rk_app_user_restaurant
(
	"id" int identity not null,
	"app_user_id" int not null,
	"restaurant_id" int not null,
	"restaurant_user_id" int
)

create table rk_admin_user
(
	"id" int identity not null,
	"user_name" nvarchar(50) not null,
    "real_name" nvarchar(50),
    "user_sex" int null,
    "user_id_no" varchar(30) null,
    "user_phone" varchar(30) null,
    "user_email" varchar(100) null,
    "user_pwd" varchar(50) not null,
    "login_time" varchar(50),
    "create_time" varchar(50) null,
    "parent_id" int,
    "parent_user_id" varchar(200),
    "rank" varchar(30) null,
    "status" int not null
    
)
create table rk_restaurant_user
(
	"id" int identity not null,
	"account" varchar(50) not null,
	"name" nvarchar(50) not null,   
    "phone" varchar(30) null,
    "email" varchar(100) null,
    "pwd" varchar(50) not null,
    "mobile" varchar(50) ,
    "login_time" varchar(50),
    "create_time" varchar(50),
    "address" varchar(200),
    "parent_id" int,
    "parent_user_id" varchar(200),
    "rank" varchar(30) null,
    "status" int not null
)
--用户表
create table "CGCU"."dbo"."rk_user"(
   "user_id" int identity not null,
   "user_name" nvarchar(50) not null,
   "real_name" nvarchar(50),
   "user_sex" int null,
   "user_id_no" varchar(30) null,
   "user_mobile" varchar(30) null,
   "user_phone" varchar(30) null,
   "user_address" nvarchar(400) null,
   "user_zip_code" varchar(10) null,
   "user_email" varchar(100) null,
   "user_pwd" varchar(50) not null,
   "user_type" int not null,
   "status" int not null,
   "user_relation" varchar(50),
   "ip" varchar(50),
   "login_time" varchar(50),
   "login_status" int,
   "create_time" varchar(50) null,
   "login_version" varchar(50) null, 
   
    constraint "PK_rk_user" primary key ("user_id")
) 
go
--菜单表
create table "CGCU"."dbo"."rk_dic_menu"(
   "menu_id" int identity not null, 
   "menu_name" varchar(200) not null,
   "menu_order" int null,
   "parent_id" int null, 
   "menu_url" varchar(300) null,  
   "status" int null,
   "modify_user" varchar(50) null,
   "modify_date" varchar(50) null,
   "create_time" varchar(50) null,
   "create_user" varchar(50) null,
    constraint "PK_rk_dic_menu" primary key ("menu_id")
)
go

--地区表
create table rk_area (
	id int identity not null,
	grade int,
	abbreviation varchar(30),
	full_name varchar(50),
	code varchar(20),
	remark varchar(200),
	parent_code varchar(200),
	status int,
	constraint "PK_rk_area" primary key ("id")
)
go 
--餐厅菜系表
create table rk_meal_style (
	id int identity not null,
	name varchar(50),
	status int,
	constraint "PK_rk_meal_style" primary key ("id")
)
go 
 
 --餐馆表
create table rk_restaurant (
	id int identity not null,
	user_id int not null,
    name varchar(100),
	province varchar(30),
	city varchar(30),
	district varchar(30),
	street varchar(30),
	other varchar(100),
	address varchar(150),
	meal_style varchar(50),
	begin_time varchar(30),
	end_time varchar(30),
	moilbe varchar(30),
	is_schedule bit,
	linkman varchar(30),
	images_1 varchar(150),
	images_2 varchar(150),
	images_3 varchar(150),
	images_4 varchar(150),
	constraint "PK_rk_restaurant" primary key ("id")
)
go
--包厢
create table rk_meal_box
(
	id int identity not null,
	name varchar(100) not null,
	images varchar(200),
	type int,
	people_number int ,
	low_consume int,
	remark varchar(200),
	restaurant_id int,
	constraint "PK_rk_restaurant" primary key ("id")
)
go
--优惠ID，优惠名称，优惠套餐，原价，优惠价，折扣幅度，开始日期，结束日期，份数，优惠说明，餐厅ID,菜肴ID，优惠类型
 create table `cugu`.`rk_meal_preferential`(
        `id` INT not null auto_increment,
       `begin_date` VARCHAR(255),
       `copies` INT,
       `cost_price` DOUBLE,
       `discount` DOUBLE,
       `end_date` VARCHAR(255),
       `explanation` VARCHAR(255),
       `images` VARCHAR(255),
       `meal_menu_id` VARCHAR(255),
       `meal_package` VARCHAR(255),
       `name` VARCHAR(255),
       `package_remark` VARCHAR(255),
       `preferential_price` DOUBLE,
       `preferential_type` INT,
       `restaurant_id` INT,
       `status` INT,
        primary key (`id`)
)charset=gbk; 
--ID，订单编号，姓名，联系方式，就餐时间，人数，包厢，状态，发送短信
create table `cugu`.`rk_meal_order`(
    `order_id` VARCHAR(255) not null,
   `box_id` INT,
   `contact` VARCHAR(255),
   `name` VARCHAR(255),
   `people_num` INT,
   `repast_date` VARCHAR(255),
   `send_sms` INT,
   `status` INT,
   `discount_price` DOUBLE,
   `cost_price` DOUBLE,
   `restaurant_id` DOUBLE,
    primary key (`order_id`)
)charset=gbk; 

drop table rk_meal_order

--订单详细表 
create table `cugu`.`rk_meal_order_detail`(
    `id` INT not null auto_increment,
   `discont` DOUBLE,
   `meal_menu_id` INT,
   `order_id` VARCHAR(255),
   `quantity` INT,
   `detail_type` INT,
   `status` INT,
    primary key (`id`)
)charset=gbk;

drop table rk_meal_order_detail

`id` INT not null auto_increment,
	       `begin_date` VARCHAR(255),
	       `copies` INT,
	       `cost_price` DOUBLE,
	       `discount` DOUBLE,
	       `end_date` VARCHAR(255),
	       `explanation` VARCHAR(255),
	       `images` VARCHAR(255),
	       `meal_menu_id` VARCHAR(255),
	       `meal_package` VARCHAR(255),
	       `name` VARCHAR(255),
	       `package_remark` VARCHAR(255),
	       `preferential_price` DOUBLE,
	       `preferential_type` INT,
	       `restaurant_id` INT,
	       `status` INT,
	       `release_status`
	       
select * from rk_restaurant as res,rk_meal_menu rmm 
where res.id=rmm.restaurant_id and address like '湖%' 
and menu_name like '%肉'
  
        
        select rmm.id,copies,cost_price,discount,begin_date,end_date,explanation,images,meal_menu_id,meal_menu_id,rmm.name,package_remark,preferential_price,preferential_type,restaurant_id,rmm.status,release_status from rk_restaurant as res,rk_meal_preferential rmm where res.id=rmm.restaurant_id

alter table rk_user
add restaurantUserId varchar(20)

select
        userdto0_.user_id as user1_14_,
        userdto0_.create_time as create2_14_,
        userdto0_.credit as credit14_,
        userdto0_.ip as ip14_,
        userdto0_.login_status as login5_14_,
        userdto0_.login_time as login6_14_,
        userdto0_.login_version as login7_14_,
        userdto0_.parent_id as parent8_14_,
        userdto0_.parent_user_id as parent9_14_,
        userdto0_.qq as qq14_,
        userdto0_.rank as rank14_,
        userdto0_.real_name as real12_14_,
        userdto0_.restaurantId as restaur13_14_,
        userdto0_.restaurantUserId as restaur14_14_,
        userdto0_.status as status14_,
        userdto0_.user_address as user16_14_,
        userdto0_.user_email as user17_14_,
        userdto0_.user_id_no as user18_14_,
        userdto0_.user_mobile as user19_14_,
        userdto0_.user_name as user20_14_,
        userdto0_.user_phone as user21_14_,
        userdto0_.user_pwd as user22_14_,
        userdto0_.user_relation as user23_14_,
        userdto0_.user_sex as user24_14_,
        userdto0_.user_type as user25_14_,
        userdto0_.user_zip_code as user26_14_ 
    from
        rk_user userdto0_ 
    where
        userdto0_.user_pwd='123123' 
        and userdto0_.user_name='yicheng'
        update rk_user set status=0 where user_name='yicheng'